package com.num2.dao.lhr.inter;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.num2.form.lhr.NextPage;
import com.num2.form.lhr.OpenMoneyForm;
import com.num2.form.lhr.UserForm;
import com.num2.model.lhr.allSelModel;


public interface IOpenAccDao {
	@Insert("insert into user_info (user_num,user_name,user_pwd,name,birthday,sex,address,telephone,id_card) " +
			"values (#{user_num},#{user_name},#{user_pwd},#{name},#{birthday},#{sex},#{adress},#{telephone},#{id_card})")
	public boolean openacc(UserForm uf);
	@Select("select user_name from user_info where user_name=#{usename}")
	public List<String> openSel(String usename);
	@Select("select user_id from user_info where user_num=#{usenum}")
	public int openuseid(String usenum);
	@Insert("insert into transaction_record (transaction_date,balance,user_id) values (#{depodate},#{balanace},#{useid})")
	public boolean openDepoInsert(OpenMoneyForm omf);
	//查询全部用户
	@Select("select ui.user_id,balance,user_num,name,address,telephone,id_card,statement  " +
			"from user_info ui,(select user_id,balance from transaction_record as a where  " +
			"transaction_id=(select max(b.transaction_id) from transaction_record as b  " +
			"where a.user_id = b.user_id)) as a where ui.user_id=a.user_id and  ui.user_power=0 group by a.user_id")
	public List<allSelModel> all();
	//查询所有用户的各数据
	@Select("select ui.user_id,balance,user_num,name,address,telephone,id_card,statement  " +
			"from user_info ui,(select user_id,balance from transaction_record as a where  " +
			"transaction_id=(select max(b.transaction_id) from transaction_record as b  " +
			"where a.user_id = b.user_id)) as a where ui.user_id=a.user_id and ui.user_power=0 group by a.user_id limit #{currentSection},10")
	public List<allSelModel> allSelect(NextPage np);
	
	//查询启用全部用户
	@Select("select ui.user_id,balance,user_num,name,address,telephone,id_card,statement  " +
			"from user_info ui,(select user_id,balance from transaction_record as a where " +
			"transaction_id=(select max(b.transaction_id) from transaction_record as b  where " +
			"a.user_id = b.user_id)) as a where ui.user_id=a.user_id and ui.user_power=0 and statement=0 group by a.user_id")
	public List<allSelModel> allEnable();
	//查询启用用户
	@Select("select ui.user_id,balance,user_num,name,address,telephone,id_card,statement  " +
			"from user_info ui,(select user_id,balance from transaction_record as a where " +
			"transaction_id=(select max(b.transaction_id) from transaction_record as b  where " +
			"a.user_id = b.user_id)) as a where ui.user_id=a.user_id and ui.user_power=0 and statement=0 group by a.user_id limit #{currentSection},10")
	public List<allSelModel> enableSelect(NextPage np);
	
	//查询冻结全部用户
	@Select("select ui.user_id,balance,user_num,name,address,telephone,id_card,statement  " +
			"from user_info ui,(select user_id,balance from transaction_record as a where " +
			"transaction_id=(select max(b.transaction_id) from transaction_record as b  where " +
			"a.user_id = b.user_id)) as a where ui.user_id=a.user_id and ui.user_power=0 and statement=1 group by a.user_id")
	public List<allSelModel> allForzen();	
	//查询冻结用户
	@Select("select ui.user_id,balance,user_num,name,address,telephone,id_card,statement  " +
			"from user_info ui,(select user_id,balance from transaction_record as a where " +
			"transaction_id=(select max(b.transaction_id) from transaction_record as b  where " +
			"a.user_id = b.user_id)) as a where ui.user_id=a.user_id and ui.user_power=0 and statement=1 group by a.user_id limit #{currentSection},10")
	public List<allSelModel> forzenSelect(NextPage np);	
	//将开启用户冻结
	@Update("update user_info set statement=1 where user_num=#{usernum}")
	public boolean closeAccDao(String usernum);
	//将冻结用户开启
	@Update("update user_info set statement=0 where user_num=#{usernum}")
	public boolean openAccDao(String usernum);
	//条件查询
	@Select("select balance,user_num,name,address,telephone,id_card,statement from user_info ui,transaction_record ts where user_num=#{query} or name=#{query} or statement=#{query}")
	public List<allSelModel> conditionQuerynum(String query);
	@Select("select balance,user_num,name,address,telephone,id_card,statement from user_info ui,transaction_record ts where user_num=#{query} or name=#{query} or statement=#{query} limit #{currentSection},10")
	public List<allSelModel> conditionQuery(NextPage np);
}
